import os
import pymysql

# 对照图片路径下的图片，删除表里无用的行
# 数据库配置
db_host = '127.0.0.1'
db_port = 3306
db_user = 'root'
db_password = 'root'
db_database = 'tfj'
db_charset = 'utf8mb4'
table = 'goods_images_eval_o2'

all_images = os.listdir('/home/tfj/datasets/image_retri10k/eval_images_o2')

image_ids = []
for image in all_images:
    image_ids.append(image.split('_')[1].split('.')[0])

if not image_ids:
    print("文件列表中没有找到 image_id，不执行删除操作。")
else:
    connection = None  # 初始化connection变量
    try:
        connection = pymysql.connect(host=db_host,
                                     port=db_port,
                                     user=db_user,
                                     password=db_password,
                                     database=db_database,
                                     charset=db_charset)

        with connection.cursor() as cursor:
            # 构建占位符字符串
            placeholders = ', '.join(['%s'] * len(image_ids))
            
            # 删除不在列表中的记录
            delete_sql = f"DELETE FROM `{table}` WHERE image_id NOT IN ({placeholders})"
            
            rows_deleted = cursor.execute(delete_sql, tuple(image_ids))
            
            connection.commit()
            
            print(f"操作完成，从表 '{table}' 中成功删除了 {rows_deleted} 条记录。")

    except pymysql.MySQLError as e:
        print(f"数据库操作失败: {e}")
        if connection:
            connection.rollback()
            print("操作已回滚。")
    finally:
        if connection:
            connection.close()
            print("数据库连接已关闭。")